package sk.stuba.fiit.foo07.genex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import sk.stuba.fiit.foo07.genex.beans.Answer;

/**
 * @author palo
 * 
 */

public class AnswerDaoDerby extends BasicDao implements AnswerDao {

    private static final String addAnswerSQL = "INSERT INTO Answer VALUES(?, ?,DEFAULT, ?)";
    private static final String printAnswers = "SELECT * FROM Answer";
    private static final String delete = "DELETE FROM Answer WHERE answerID=?";
    private static final String getAnswer = "SELECT * FROM Answer WHERE answerID=?";
    private static final String getAnswersByQuestion = "SELECT * FROM Answer WHERE questionID=?";
    private static final String update = "UPDATE Answer SET Text=?,IsCorrect=?,questionID=? WHERE AnswerID=?";
    protected ResultSet rs;
    protected PreparedStatement pst;
    protected Answer a;
    protected ArrayList<Answer> a_list;

    public AnswerDaoDerby(Connection con) {
        super(con);
    }

    public AnswerDaoDerby() throws Exception {
        super();
    }

    public void printAnswers() {
        try {
            a_list = new ArrayList<Answer>();
            pst = con.prepareStatement(printAnswers);
            rs = pst.executeQuery();

            while (rs.next()) {
                a = new Answer();
                a.setAnswerID(rs.getInt("answerID"));
                a.setIsCorrect(rs.getBoolean(("IsCorrect")));
                a.setQuestionID(rs.getInt("QuestionID"));
                a.setText(rs.getString("Text"));
                System.out.println("odpoved: " + a.getText() + " "
                        + a.getIsCorrect() + " " + a.getQuestionID() + " "
                        + a.getAnswerID());
            }
            rs.close();

        } catch (SQLException e) {
            a_list = null;
            a = null;
            pst = null;
            rs = null;
            return;
        }

    }

    @Override
    public void addAnswerToQuestion(Integer questionID, Answer toAdd)
            throws SQLException {
        try {
            PreparedStatement st = con.prepareStatement(addAnswerSQL,
                    Statement.RETURN_GENERATED_KEYS);

            st.setString(1, toAdd.getText());
            st.setBoolean(2, toAdd.getIsCorrect());
            st.setInt(3, questionID);

            st.execute();
            ResultSet rs = st.getGeneratedKeys();
            rs.next();

            toAdd.setAnswerID(rs.getInt(1));
            rs.close();

        } catch (SQLException e) {
            pst = null;
            rs = null;
            throw e;
        }
    }

    @Override
    public void addAnswersToQuestion(Integer questionID,
            ArrayList<Answer> answers) throws SQLException {

        for (Answer a : answers) {
            addAnswerToQuestion(questionID, a);
        }
    }

    @Override
    public void deleteAnswer(Answer toDelete) {
        try {
            pst = con.prepareStatement(delete);
            pst.setInt(1, toDelete.getAnswerID());
            pst.execute();

        } catch (SQLException e) {
            pst = null;
            return;
        }
    }

    @Override
    public Answer getAnswerByID(Integer answerID) throws SQLException {

        try {
            a = new Answer();
            pst = con.prepareStatement(getAnswer);
            pst.setInt(1, answerID);
            rs = pst.executeQuery();

            rs.next();
            a.setAnswerID(answerID);
            a.setIsCorrect(rs.getBoolean(("IsCorrect")));
            a.setQuestionID(rs.getInt("QuestionID"));
            a.setText(rs.getString("Text"));
            rs.close();

        } catch (SQLException e) {
            a = null;
            pst = null;
            rs = null;
            throw e;
        }
        return a;
    }

    @Override
    public ArrayList<Answer> getAnswersByQuestionID(Integer questionID)
            throws SQLException {
        try {

            pst = con.prepareStatement(getAnswersByQuestion,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, questionID);
            rs = pst.executeQuery();

            rs.last();
            a_list = new ArrayList<Answer>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                a = new Answer();
                a.setAnswerID(rs.getInt("answerID"));
                a.setIsCorrect(rs.getBoolean(("IsCorrect")));
                a.setQuestionID(rs.getInt("QuestionID"));
                a.setText(rs.getString("Text"));
                a_list.add(a);
            }
            rs.close();

        } catch (SQLException e) {
            a = null;
            pst = null;
            rs = null;
            throw e;
        }
        return a_list;
    }

    @Override
    public void updateAnswer(Answer toUpdate) throws SQLException {
        try {
            pst = con.prepareStatement(update);

            Short a;
            if (toUpdate.getIsCorrect())
                a = 1;
            else
                a = 0;
            pst.setString(1, toUpdate.getText());
            pst.setShort(2, a);
            pst.setInt(3, toUpdate.getQuestionID());
            pst.setInt(4, toUpdate.getAnswerID());
            pst.executeUpdate();

        } catch (SQLException e) {
            pst = null;
            throw e;
        }
    }
}
